<?php

namespace app\admin\controller;


use think\Db;
use think\Validate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class Student extends Base
{
    public function index()
    {
        extract(input());
        $where = [];
        if (isset($name) && !empty($name)) {
            $where['name'] = ['like', '%' . $name . '%'];
        }
        if (isset($school_id) && !empty($school_id)) {
            $where['s.id'] = $school_id;
            // 如果 school_id 不为空，说明本次搜索选择了学校。
            // 既然知道了学校，就可以将该学校下的专业查询出来，循环输出在搜索下拉框中，如此，就可以在搜索完成时选中选择的专业了。
            $class = Db::name('tp_major')->where('school_id', $school_id)->select();
        } else {
            // 本次没有选择学校进行搜索，用一个空数组代替。
            $class = [];
        }
        if (isset($major_id) && !empty($major_id)) {
            $where['m.id'] = $major_id;
            $room = Db::name('tp_class')->where('major_id', $major_id)->select();
        } else {
            // 本次没有选择学校进行搜索，用一个空数组代替。
            $room = [];
        }
        if (isset($class_id) && !empty($class_id)) {
            $where['c.id'] = $class_id;
        }
        $data = Db::name('tp_student')
            ->alias('stu')
            ->join('tp_class c', 'c.id=stu.class_id')
            ->join('tp_major m', 'm.id=c.major_id')
            ->join('tp_school s', 's.id=m.school_id')
            ->field('stu.*,m.major_name,s.school_name,c.class_name')
            ->order('id Desc')
            ->where($where)
            ->paginate('3', false, ['query' => input()]);
        //print_r($data);
        $page = $data->render();
        // 查询学校 - 搜索的下拉框使用
        $school = Db::name('tp_school')->select();
        return $this->fetch('index_Ajax', [
            'data' => $data,
            'page' => $page,
            'school' => $school,
            'classroom' => $class,
            'classes' => $room
        ]);
    }

    public function add()
    {
        // $data = Db::name('tp_class')->select();
        $data = Db::name('tp_school')->select();
        $this->assign('data', $data);
        return $this->fetch('add_Ajax');
    }

    public function Get_major()
    {
        $schoolId = input('school_id');
        $major = Db::name('tp_major')
            ->where('school_id', $schoolId)
            ->select();
        $options = ["<option value=''>请选择专业</option>"];
        foreach ($major as $v) {
            $options[] = "<option value='" . $v['id'] . "'>" . $v['major_name'] . "</option>";
        }
        return $options;
    }

    public function Get_class()
    {
        $majorID = input('major_id');
        $major = Db::name('tp_class')
            ->where('major_id', $majorID)
            ->select();
        $options = ["<option value=''>请选择班级</option>"];
        foreach ($major as $v) {
            $options[] = "<option value='" . $v['id'] . "'>" . $v['class_name'] . "</option>";
        }
        return $options;
    }

    public function addPost()
    {
        $data = input();
        $res = Db::name('tp_student')->insert($data);
        if ($res) {
            $this->success('添加成功');
        } else {
            $this->error('添加失败');
        }
    }

    public function addPostByAjax()
    {
        // 接收数据
        $data = input();
        unset($data['file']);
        // 入库
        $add = Db::name('tp_student')->insert($data);

        if ($add) {
            return json(['code' => 1, 'msg' => '添加成功']);
        } else {
            return json(['code' => 2, 'msg' => '添加失败']);
        }
    }

/* 获取某个专业下的所有班级
*/
    public function getClassByMajor()
    {
        $majorId = input('major_id');
        $class = Db::name('tp_class')
            ->where('major_id', $majorId)
            ->select();
        $options = [
            "<option value=''>请选择班级</option>"
        ];
        foreach ($class as $v) {
            $options[] = "<option value='".$v['id']."'>".$v['class_name']."</option>";
        }
        return $options;
    }

    /*
     * 编辑显示
     */
    public function edit()
    {
        $id = input('id');
        $student = Db::name('tp_student')->find($id);
        $school = Db::name('tp_school')->select();
        // 找到该学生所在专业 ID
        $majorId = Db::name('tp_class')->where('id', $student['class_id'])->value('major_id');
        // 找到该学生所在学校的 ID
        $schoolId = Db::name('tp_major')->where('id', $majorId)->value('school_id');
        // 查询该学生所在学校下的专业
        $major = Db::name('tp_major')->where('school_id', $schoolId)->select();
        // 查询该学生所在专业下的班级
        $classroom = Db::name('tp_class')->where('major_id', $majorId)->select();
        return $this->fetch('edit', [
            'data' => $student,
            'school' => $school,
            'majorId' => $majorId,
            'schoolId' => $schoolId,
            'major' =>$major,
            'classroom' => $classroom
        ]);
    }

    /*
     * 编辑提交
     */
    public function editPost()
    {
        $data = input();
        unset($data['file']);
        $rule = [
            'name'  => 'require',
            'sex' => 'require|integer',
            'age' => 'require|integer',
            'idcard' => 'require|max:18',
            'address' => 'require',
            'class_id' => 'require|integer'
        ];

        $msg = [
            'name.require' => '姓名不能为空',
            'sex.require' => '性别不能为空',
            'sex.integer' => '性别传值错误',
            'age.require' => '年龄不能为空',
            'age.integer' => '年龄传值错误',
            'idcard.require' => '身份证号不能为空',
            'idcard.max' => '身份证号不能超过18位',
            'address.require' => '家庭住址不能为空',
            'class_id.require' => '班级不能为空',
            'class_id.integer' => '班级传值错误',
        ];

        $validate = new Validate($rule, $msg);
        $result   = $validate->check($data);

        if (true !== $result) {
            $this->error($validate->getError());
        }

        $edit = Db::name('tp_student')->where('id', $data['id'])->update($data);

        if ($edit) {
            return json(['code' => 1, 'msg' => '保存成功']);
        } else {
            return json(['code' => 2, 'msg' => '保存失败']);
        }
    }
    public function del()
    {
        $id = input('id');
        $data = Db::name('tp_student')->delete($id);
        if ($data == 1) {
            $this->success('删除成功', 'student/index');
        } else {
            $this->error('删除失败');
        }
    }

    public function delByAjax()
    {
        $data = input();
        $del = Db::name('tp_student')->delete($data);
        if ($del) {
            return json(['code' => 1, 'msg' => '删除成功']);
        } else {
            return json(['code' => 2, 'msg' => '删除失败']);
        }
    }

    public function deleteByAjax()
    {
        $ids = input('ids/a');
        $del = Db::name('tp_student')->delete($ids);
        if ($del) {
            return json(['code' => 1, 'msg' => '删除成功']);
        } else {
            return json(['code' => 2, 'msg' => '删除失败']);
        }
    }

    public function batchDelete()
    {
        $ids = input('ids/a');
        //print_r($ids);die;
        $del = Db::name('tp_student')->delete($ids);
        if ($del) {
            $this->success('删除成功', 'student/index');
        } else {
            $this->error('删除失败');
        }
    }

    // 上传图片
    public function upload()
    {
        // 获取表单上传文件 例如上传了001.jpg
        $file = request()->file('file');
        // 移动到框架应用根目录/public/uploads/ 目录下
        if ($file) {
            $info = $file->validate(['size'=>1024 * 1024 *3,'ext'=>'jpg,png'] )->move(ROOT_PATH . 'public' . DS . 'uploads');
            if ($info) {
                return json([
                    'code' => 1,
                    'msg' => '上传成功',
                    'data' => [
                        'src' => '/uploads/' . $info->getSaveName()
                    ]
                ]);
            } else {
                return json([
                    'code' => 2,
                    'msg' => $file->getError(),
                    'data' => ''
                ]);
            }
        }
    }
    //导出Excel
    public function exportExcel()
    {
        // 查询要导出的数据
        $data = Db::name('tp_student')
            ->join('tp_class','tp_student.class_id=tp_class.id')
            ->select();
        // 实例化
        $spreadsheet = new Spreadsheet();
        // 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();

        // 获取单元格
        $cellA = $sheet->getCell('A1');
        // 设置单元格的值
        $cellA->setValue('ID');
        // 设置 A 列 列宽
        $sheet->getColumnDimension('A')->setWidth(10);
        // 设置第一行 行高
        $sheet->getRowDimension(1)->setRowHeight(20);

        $cellB = $sheet->getCell('B1');
        $cellB->setValue('学生头像');
        $sheet->getColumnDimension('B')->setWidth(30);

        $cellC = $sheet->getCell('C1');
        $cellC->setValue('学生姓名');
        $sheet->getColumnDimension('C')->setWidth(10);

        $cellD = $sheet->getCell('D1');
        $cellD->setValue('学生性别');
        $sheet->getColumnDimension('D')->setWidth(10);

        $cellE = $sheet->getCell('E1');
        $cellE->setValue('学生年龄');
        $sheet->getColumnDimension('E')->setWidth(10);

        $cellF = $sheet->getCell('F1');
        $cellF->setValue('身份证号');
        $sheet->getColumnDimension('F')->setWidth(20);

        $cellG = $sheet->getCell('G1');
        $cellG->setValue('家庭地址');
        $sheet->getColumnDimension('G')->setWidth(25);

        $cellG = $sheet->getCell('H1');
        $cellG->setValue('所属班级');
        $sheet->getColumnDimension('H')->setWidth(40);



        // 设置样式 标题栏
        $styleArray = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '黑体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 设置样式 正文
        $styleArrayBody = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '宋体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 应用样式
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('B1')->applyFromArray($styleArray);
        $sheet->getStyle('C1')->applyFromArray($styleArray);
        $sheet->getStyle('D1')->applyFromArray($styleArray);
        $sheet->getStyle('E1')->applyFromArray($styleArray);
        $sheet->getStyle('F1')->applyFromArray($styleArray);
        $sheet->getStyle('G1')->applyFromArray($styleArray);
        $sheet->getStyle('H1')->applyFromArray($styleArray);

        // 从 A2 开始填充数据
        foreach ($data as $k => $v) {
            $n = $k + 2;
            // 获取单元格
            $cellA = $sheet->getCell('A'.$n);
            // 设置单元格的值
            $cellA->setValue($v['id']);

            $cellB = $sheet->getCell('B'.$n);
            $cellB->setValue($v['image']);

            $cellC = $sheet->getCell('C'.$n);
            $cellC->setValue($v['name']);

            $cellD = $sheet->getCell('D'.$n);
            $cellD->setValue($v['sex']);

            $cellE = $sheet->getCell('E'.$n);
            $cellE->setValue($v['age']);

            $cellF = $sheet->getCell('F'.$n);
            $cellF->setValue($v['idcard']);

            $cellG = $sheet->getCell('G'.$n);
            $cellG->setValue($v['address']);

            $cellH = $sheet->getCell('H'.$n);
            $cellH->setValue($v['class_name']);
            // 再给表格体设置样式
            $sheet->getStyle('A'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('B'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('C'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('D'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('E'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('F'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('G'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('H'.$n)->applyFromArray($styleArrayBody);
        }

        // 下载文件名
        $file_name = '学生列表.xlsx';
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename='.$file_name);
        header('Cache-Control:max-age=0');
        // 下载
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
    //导入
    public function Excel_export()
    {
        // 接收文件
        $file = $_FILES['file']['tmp_name'];
        // 创建读操作
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        // 打开文件、载入excel表格
        $spreadsheet = $reader->load($file);
        // 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();


        // 获取总行数
        $highestRow = $sheet->getHighestRow();

        // 存放插入成功的记录
        $successLog = [];
        // 存放插入失败的记录
        $failLog = [];

        // 从第二行开始读取表格数据，循环写入数据库
        for ($i = 2; $i <= $highestRow; $i++) {
            // 第 1 次循环获取第 2列 第 2 行单元格的值，第 2 次循环获取第 3 列 第 3 行单元格的值
            $image = $sheet->getCellByColumnAndRow(2, $i)->getValue();
            $name = $sheet->getCellByColumnAndRow(3, $i)->getValue();
            $sex = $sheet->getCellByColumnAndRow(4, $i)->getValue();
            $age = $sheet->getCellByColumnAndRow(5, $i)->getValue();
            $idcard = $sheet->getCellByColumnAndRow(6, $i)->getValue();
            $address = $sheet->getCellByColumnAndRow(7, $i)->getValue();
            $class_id = $sheet->getCellByColumnAndRow(8, $i)->getValue();
            // 假设数据表有以下字段

            $data = [
                'image' => $image,
                'name' => $name,
                'sex' => $sex,
                'age' => $age,
                'idcard' => $idcard,
                'address' => $address,
                'class_id' => $class_id
            ];
            $insert = Db::name('tp_student')->insert($data);
            if ($insert) {
                $successLog[] = '第' . $i . '条，插入成功';
            } else {
                $failLog[] = '第' . $i . '条，插入失败';
            }
        }
        // 将成功数和失败数返回给 ajax
        return json(['success' => count($successLog), 'fail' => count($failLog)]);
    }
}